﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace VIT.Provider
{
    public class Link : Link_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public Link(int Id = -1,
                        string LanguageId = "",
                        string Name = "",
                        string URL = "",
                        int Orders = 50,
                        bool Published = true)
        {
            this.Id = Id;
            this.LanguageId = LanguageId;
            this.Name = Name;
            this.URL = URL;
            this.Orders = Orders;
            this.Published = Published;
        }
        #endregion

        #region List
        public DataTable GetAll(string SortColumn)
        {
            if (SortColumn == "") SortColumn = "Orders";
            string strQuery = "select Id, Name, URL, Orders, Published from Link li left join Link_Language la on Id=LinkId and LanguageID=@LanguageID where Deleted=0 order by " + SortColumn;            
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return ExecuteDataset(false, strQuery, LanguageId).Tables[0];
        }

        public bool Delete(int delete, int id)
        {
            string strQuery = "update Link set Deleted=@Deleted, ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete==1)?user:"");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            if (ExecuteNonQuery(false, strQuery, Id, D, User, DeleteByUser, Now) > 0)
            {
                strQuery = "update Link_Language set Published=@Published where LinkId=@ID";
                SqlParameter ID = new SqlParameter("@ID", id);
                SqlParameter P = new SqlParameter("@Published", (delete == 0) ? 1 : 0);
                return ExecuteNonQuery(false, strQuery, ID, P) > 0;
            }

            return false;
        }

        public bool Publish(int publish, int id)
        {
            string strQuery = "update Link_Language set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where LinkId=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, P, User, Now) > 0;
        }

        public bool Order(string order, int id)
        {
            string strQuery = "update Link_Language set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where LinkId=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, User, Now) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select Name, URL, Orders, Published from Link left join Link_Language on Id=LinkId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(false, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Name = dt.Rows[0]["Name"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.URL = dt.Rows[0]["URL"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];
                }
            }
            else this.Id = -1;
        }

        public bool Exist()
        {
            string strQuery = "select Id from Link where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(false, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid)
        {
            string strQuery = "select LinkId, LanguageID from Link_Language where LinkId=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(false, strQuery, Id, LanguageId) != null;
        }

        public int Save()
        {
            if (Exist())
            {
                Delete(0, this.Id);
                if (UpdateObj() > 0)
                {
                    if (Exist(this.Id, this.LanguageId)) return UpdateObjLang(true);
                    else return InsertObjLang();
                }
            }
            else
            {
                this.Id = InsertObj(true);
                if (this.Id > 0) return InsertObjLang();
            }
            return 0;
        }

        public int InsertObj(bool isTransaction)
        {
            string sql = "insert into Link(URL, Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@URL, 0, @Now, @Now, @User, @User)";

            SqlParameter URL = new SqlParameter("@URL", this.URL);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sql, URL, User, Now);
        }

        public int InsertObjLang()
        {
            string sqlLang = "insert into Link_Language (LinkId, LanguageId, Name, Orders, Published, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@LinkId, @LanguageId, @Name, @Orders, @Published, @Now, @Now, @User, @User)";
            SqlParameter LinkId = new SqlParameter("@LinkId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Name = new SqlParameter("@Name", this.Name);
            SqlParameter OrderVal = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sqlLang, LinkId, LanguageId, Name, OrderVal, Published, User, Now);
        }

        public int UpdateObj()
        {
            string sqlCat = "update Link set URL=@URL, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter URL = new SqlParameter("@URL", this.URL);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sqlCat, Id, URL, User, Now);
        }

        public int UpdateObjLang(bool isTransaction)
        {
            string sqlLang = "update Link_Language set Name=@Name, Orders=@OrderVal, Published=@Published, ModifyDate=@Now, ModifyByUser=@User where LinkId=@LinkId and LanguageId=@LanguageId";
            SqlParameter LinkId = new SqlParameter("@LinkId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Name", this.Name);
            SqlParameter OrderVal = new SqlParameter("@OrderVal", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlLang, LinkId, LanguageId, Title, OrderVal, Published, User, Now);
        }
        #endregion

        #region Trash
        public DataTable GetRecycleBin(string SortColumn)
        {
            string strQuery;
            if (SortColumn == "")
                strQuery = "select Id, URL, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Link where Deleted=1";
            else
                strQuery = "select Id, URL, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Link where Deleted=1 order by " + SortColumn;
            return ExecuteDataset(false, strQuery).Tables[0];
        }

        public bool Cleared(string id)
        {
            string strQuery = "delete from Link where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(false, strQuery, Id)>0;
        }
        #endregion
    }
}
